package com.linlz.read;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import static org.apache.poi.ss.usermodel.Cell.*;


public class Demo1 {
    public static void main(String[] args) throws IOException, IOException {
        FileInputStream input = new FileInputStream("C:\\Users\\112\\Desktop\\Hello.xlsx");
        //1.获取工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(input);
        //
        int numberOfSheets = workbook.getNumberOfSheets();
        //判断获取工作簿是否为空
        if (0 != numberOfSheets) {
            for (int i = 0; i < numberOfSheets; i++) {
                //2.获取工作表(通过索引或者名称获取)
                XSSFSheet sheet = workbook.getSheetAt(i);
                //3.获取行（增强for循环
                for (Row row : sheet) {
                    //4.获取单元格
                    for (Cell cell : row) {
                        //获取单元格中的内容
                        String value = cell.getStringCellValue();
                        System.out.println(value);
                    }
                }
            }
        }


        ArrayList<Object> list1 = new ArrayList<>();
        ArrayList<List> list2 = new ArrayList<>();
        //普通for循环
        for (int i = 0; i < numberOfSheets; i++) {
            //如果excel表为空则结束循环
            if (numberOfSheets == 0) {
                break;
            }
            XSSFSheet sheet = workbook.getSheetAt(i);
            int firstRowNum = sheet.getFirstRowNum();
            int lastRowNum = sheet.getLastRowNum();
            for (int j = firstRowNum; j <= lastRowNum; j++) {
                XSSFRow row = sheet.getRow(j);
                if (null != row) {
                    short cellNum = row.getLastCellNum();
                    for (int k = 0; k < cellNum; k++) {
                        XSSFCell cell = row.getCell(k);
                        if (null != cell) {
//                            String value = cell.getStringCellValue();
//                            System.out.println(value);
                            String value = "";
                            //解决excel类型问题
                            switch (cell.getCellType()) {
                                //数值型
                                case CELL_TYPE_NUMERIC:
                                    if (DateUtil.isCellDateFormatted(cell)) {
                                        //如果是date类型则 ，获取该cell的date值
                                        Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                                        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                        value = format.format(date);
                                    }else {// 纯数字
                                        BigDecimal big= new BigDecimal(cell.getNumericCellValue());
                                        value = big.toString();
                                        //解决1234.0  去掉后面的.0
                                        if(null!= value &&!"".equals(value.trim())){
                                            String[] item = value.split("[.]");
                                            if(1<item.length&&"0".equals(item[1])){
                                                value = item[0];
                                            }
                                        }
                                    }
                                    break;
                                //字符串类型
                                case CELL_TYPE_STRING:
                                    value = cell.getStringCellValue();
                                    break;
                                // 公式类型
                                case CELL_TYPE_FORMULA:
                                    //读公式计算值
                                    value = String.valueOf(cell.getNumericCellValue());
                                    if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
                                        value = value;
                                    }
                                    break;
                                // 布尔类型
                                case CELL_TYPE_BOOLEAN:
                                    value = " "+ cell.getBooleanCellValue();
                                    break;
                                default:
                                    value = cell.getStringCellValue();
                            }
                            if("null".endsWith(value.trim())){
                                value= "";
                            }
                            System.out.println(value);
                        }
                    }
                }
            }
        }


        //释放资源
       workbook.close();
    }



/**
     * @Description：对表格中数值进行格式化
     * @param cell
     * @return
     */

    //解决excel类型问题，获得数值



}
